@file:Suppress("FunctionName")

package com.zxy.supplier_system.server.repository

import com.zxy.supplier_system.server.controller.EmployeeController
import com.zxy.supplier_system.server.entity.*
import com.zxy.supplier_system.server.utils.SpecificationUtils
import com.zxy.supplier_system.server.utils.joinOnce
import jakarta.persistence.criteria.Expression
import jakarta.persistence.criteria.JoinType
import org.springframework.data.jpa.domain.Specification
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.JpaSpecificationExecutor

interface EmployeeRepository : JpaRepository<Employee, Int>, JpaSpecificationExecutor<Employee> {

    fun findByPhoneNumberAndPasswordAndSystemService(
        phoneNumber: String,
        password: String,
        systemService: SystemService
    ): Employee?

    fun existsByPhoneNumberAndSystemService_Id(phoneNumber: String, systemService_id: String): Boolean

    fun findAllBySetting_SubscribeAllMarketOrderSubmittedWechatNotificationIsTrueAndSystemService(systemService: SystemService): List<Employee>

    fun findByIdAndSystemService(id: Int, systemService: SystemService): Employee?

    object Specifications {

        fun haveMarketOrder(): Specification<Employee> {
            return Specification { root, _, criteriaBuilder ->
                criteriaBuilder.isNotEmpty(root.get("marketOrders"))
            }
        }

        fun haveMarketsOrder(marketId: Int): Specification<Employee> {
            return Specification { root, query, criteriaBuilder ->
                val employeeMarketOrderJoin = root.joinOnce<Employee, MarketOrder>("marketOrders", JoinType.LEFT)
                val marketOrderMarketJoin =
                    employeeMarketOrderJoin.joinOnce<MarketOrder, Market>("market", JoinType.LEFT)
                query.groupBy(root.get<Int>("id"))
                    .where(criteriaBuilder.equal(marketOrderMarketJoin.get<Int>("id"), marketId)).restriction
            }
        }

        fun haveProcurement(): Specification<Employee> {
            return Specification { root, _, criteriaBuilder ->
                criteriaBuilder.isNotEmpty(root.get("createdProcurements"))
            }
        }

        fun haveProcurementReturn(): Specification<Employee> {
            return Specification { root, _, criteriaBuilder ->
                criteriaBuilder.isNotEmpty(root.get("createdProcurementReturns"))
            }
        }

        fun haveCreatedMarketCheckInventoryRecord(): Specification<Employee> {
            return Specification { root, _, criteriaBuilder ->
                criteriaBuilder.isNotEmpty(root.get("createdMarketCheckInventoryRecords"))
            }
        }

        fun createdMarketCheckInventoryRecord_MarketIdEqual(marketId: Int): Specification<Employee> {
            return Specification { root, query, criteriaBuilder ->
                query.groupBy(root.get<Int>("id"))
                criteriaBuilder.equal(
                    root.join<Employee, MarketCheckInventoryRecord>("createdMarketCheckInventoryRecords", JoinType.LEFT)
                        .join<MarketCheckInventoryRecord, Market>("market").get<Int>("id"), marketId
                )
            }
        }

        fun haveCreatedCheckInventoryRecord(): Specification<Employee> {
            return Specification { root, _, criteriaBuilder ->
                criteriaBuilder.isNotEmpty(root.get("createdCheckInventoryRecords"))
            }
        }

        fun orderBySaleManager(): Specification<Employee> {
            return Specification { root, query, cb ->
                val order = cb.desc(
                    existSaleManager(root.get("id")).toPredicate(root, query, cb)
                )
                query.orderBy(order)
                null
            }
        }

        fun existSaleManager(saleManagerId: Int?): Specification<Employee> {
            return Specification { root, query, criteriaBuilder ->
                existSaleManager(saleManagerId?.let { criteriaBuilder.literal(saleManagerId) }).toPredicate(
                    root,
                    query,
                    criteriaBuilder
                )
            }
        }

        fun existSaleManager(saleManagerIdExpression: Expression<Int>?): Specification<Employee> {
            return Specification<Employee> { _, query, criteriaBuilder ->
                if (saleManagerIdExpression == null) {
                    null
                } else {
                    val marketSubquery = query.subquery(Market::class.java)
                    val marketRoot = marketSubquery.from(Market::class.java)
                    marketSubquery.select(marketRoot).where(
                        criteriaBuilder.equal(
                            marketRoot.join<Market, Employee>("saleManager").get<Int>("id"),

                            saleManagerIdExpression
                        ),
                    )
                    criteriaBuilder.exists(marketSubquery)
                }

            }
        }

        fun existsSaleManager(): Specification<Employee> {
            return Specification { root, query, criteriaBuilder ->
                val marketSubquery = query.subquery(Market::class.java)
                val marketRoot = marketSubquery.from(Market::class.java)
                marketSubquery.select(marketRoot).where(
                    criteriaBuilder.equal(
                        root.get<Int>("id"),
                        marketRoot.get<Employee>("saleManager").get<Int>("id")
                    )
                )
                criteriaBuilder.exists(marketSubquery)
            }
        }

        fun fromQueryEmployeeRequest(queryEmployeeRequest: EmployeeController.QueryEmployeeRequest): Specification<Employee> {
            return Specification.allOf(
                roleIn(queryEmployeeRequest.roleIds),
                phoneNumberContains(queryEmployeeRequest.phoneNumber),
                nameContains(queryEmployeeRequest.name),
                marketNameContains(queryEmployeeRequest.marketName),
                Specification.anyOf(
                    nameContains(queryEmployeeRequest.searchInput),
                    marketNameContains(queryEmployeeRequest.searchInput),
                ),
            )
        }

        fun roleIn(roleIds: List<Int>): Specification<Employee> {
            if (roleIds.isEmpty()) {
                return Specification.where(null)
            }

            return Specification { root, query, criteriaBuilder ->
                val employeeRoleSubquery = query.subquery(Int::class.java)
                val employeeRoleSubqueryRoot = employeeRoleSubquery.from(EmployeeRole::class.java)


                val `in` = criteriaBuilder.`in`(
                    employeeRoleSubqueryRoot.joinOnce<EmployeeRole, Role>("role", JoinType.LEFT).get<Int>("id")
                )
                roleIds.forEach {
                    `in`.value(it)
                }
                employeeRoleSubquery.where(
                    criteriaBuilder.equal(
                        employeeRoleSubqueryRoot.join<EmployeeRole, Employee>("employee").get<Int>("id"),
                        root.get<Int>("id")
                    ),
                    `in`,
                )
                criteriaBuilder.exists(employeeRoleSubquery)
            }
        }

        fun roleNameNotEqual(roleName: String?): Specification<Employee> {
            if (roleName.isNullOrEmpty()) {
                return Specification.where(null)
            }
            return Specification { root, query, criteriaBuilder ->
                val employeeRoleSubquery = query.subquery(Int::class.java)
                val employeeRoleSubqueryRoot = employeeRoleSubquery.from(EmployeeRole::class.java)

                employeeRoleSubquery.where(
                    criteriaBuilder.equal(
                        employeeRoleSubqueryRoot.join<EmployeeRole, Employee>("employee").get<Int>("id"),
                        root.get<Int>("id")
                    ),
                    criteriaBuilder.notEqual(
                        employeeRoleSubqueryRoot.joinOnce<EmployeeRole, Role>(
                            "role",
                            JoinType.LEFT
                        ).get<String>("name"), roleName
                    ),
                )
                criteriaBuilder.exists(employeeRoleSubquery)
            }
        }

        fun phoneNumberContains(phoneNumber: String?): Specification<Employee> {
            return SpecificationUtils.contains({ root -> root.get("phoneNumber") }, phoneNumber)
        }

        fun nameContains(name: String?): Specification<Employee> {
            return SpecificationUtils.contains({ root -> root.get("name") }, name)
        }

        fun marketNameContains(name: String?): Specification<Employee> {
            return if (name.isNullOrBlank())
                SpecificationUtils.empty()
            else
                Specification { root, query, criteriaBuilder ->

                    val marketSubQuery = query.subquery(Int::class.java)
                    val marketSubQueryRoot = marketSubQuery.from(Market::class.java)
                    val saleManagerEmployeeJoin = marketSubQueryRoot.joinOnce<Market, Employee>("saleManager")
                    val marketShoppingGuideSubQuery = marketSubQuery.subquery(Int::class.java)
                    val marketShoppingGuideSubQueryRoot =
                        marketShoppingGuideSubQuery.from(MarketShoppingGuide::class.java)
                    val marketShoppingGuideSubQueryEmployeeJoin =
                        marketShoppingGuideSubQueryRoot.joinOnce<MarketShoppingGuide, Employee>(
                            "employee"
                        )
                    marketSubQuery
                        .where(
                            criteriaBuilder.like(marketSubQueryRoot.get("name"), "%$name%"),
                            criteriaBuilder.or(
                                criteriaBuilder.equal(saleManagerEmployeeJoin.get<Int>("id"), root.get<Int>("id")),
                                criteriaBuilder.exists(
                                    marketShoppingGuideSubQuery.select(
                                        marketShoppingGuideSubQueryEmployeeJoin.get<Int>("id")
                                    ).where(
                                        criteriaBuilder.equal(
                                            marketShoppingGuideSubQueryRoot.get<Market>("market").get<Int>("id"),
                                            marketSubQueryRoot.get<Int>("id")
                                        ),
                                        criteriaBuilder.like(
                                            marketShoppingGuideSubQueryEmployeeJoin.get("name"),
                                            "%$name%"
                                        ),
                                    )
                                )
                            ),
                        )
                        .select(marketSubQueryRoot.get<Int>("id"))

                    criteriaBuilder.and(criteriaBuilder.exists(marketSubQuery))
                }
        }

        fun haveDirectSaleOrder(): Specification<Employee> {
            return Specification { root, _, criteriaBuilder ->
                criteriaBuilder.isNotEmpty(root.get("createdDirectSaleOrders"))
            }
        }

        fun haveMarketReturnOrder(): Specification<Employee> {
            return Specification { root, _, criteriaBuilder ->
                criteriaBuilder.isNotEmpty(root.get("marketReturnOrders"))
            }
        }

        fun haveMarketsReturnOrder(marketId: Int): Specification<Employee> {
            return Specification { root, query, criteriaBuilder ->
                val employeeMarketOrderJoin =
                    root.joinOnce<Employee, MarketReturnOrder>("marketReturnOrders", JoinType.LEFT)
                val marketOrderMarketJoin =
                    employeeMarketOrderJoin.joinOnce<MarketReturnOrder, Market>("market", JoinType.LEFT)
                query.groupBy(root.get<Int>("id"))
                    .where(criteriaBuilder.equal(marketOrderMarketJoin.get<Int>("id"), marketId)).restriction
            }
        }

        fun haveMarketsSaleDocument(marketId: Int): Specification<Employee> {
            return Specification { root, query, criteriaBuilder ->
                val employeeMarketOrderJoin =
                    root.joinOnce<Employee, MarketSaleDocument>("marketReturnOrders", JoinType.LEFT)
                val marketOrderMarketJoin =
                    employeeMarketOrderJoin.joinOnce<MarketSaleDocument, Market>("market", JoinType.LEFT)
                query.groupBy(root.get<Int>("id"))
                    .where(criteriaBuilder.equal(marketOrderMarketJoin.get<Int>("id"), marketId)).restriction
            }
        }

        fun haveMarketSaleDocument(): Specification<Employee> {
            return Specification { root, _, criteriaBuilder ->
                criteriaBuilder.isNotEmpty(root.get("marketSaleDocuments"))
            }
        }


    }

    fun findAllBySystemService(systemService: SystemService): List<Employee>

    fun findByWechatUserInfo_MiniProgramOpenIdAndSystemService_Id(openid: String, systemServiceId: String): Employee?

}
